package com.OnlineHouseLeaseSystem.common;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.OnlineHouseLeaseSystem.database.DBManager;

public class NoticeManager {
	private static final String NOTICE_ID = "notice_id";
	private static final String USER_ID = "user_id";
	private static final String HOUSE_ID = "house_id";
	private static final String KNOW = "know";
	private static final String HOUSE_TITLE = "house_title";
	private static final String USER_NAME = "user_name";
	private static final String PHONE_NUMBER = "phone_number";
	
	private final static String LOCK_TABLE = "LOCK TABLE notice IN EXCLUSIVE MODE; ";
	
	private final static String GET_MAX_NOTICE_ID = "SELECT MAX(notice_id) FROM notice";
	private final static String INSERT_NOTICE_HEAD = "INSERT INTO notice VALUES ( '";
    private final static String INSERT_NOTICE_FOOT = "' ) ";
    private final static String HAS_POSTED_NOTICE_CHECK = "SELECT * FROM notice";
    private final static String GET_USER_NOTICE = "SELECT * FROM notice,house_information" +
    							" WHERE notice.house_id=house_information.house_id AND notice.user_id='";
    private final static String GET_NOTICE_AND_USER_INFORMATION = "SELECT * FROM notice,user_information,house_information" +
    						   									  " WHERE notice.user_id=user_information.user_id" +
    						   									  " AND notice.house_id=house_information.house_id" +
    						   									  " AND notice.know='未读'" +
    						   									  " AND house_information.user_id='";
    private final static String CHANGE_NOTICE_AS_KNOWN = "UPDATE notice SET know='已读' WHERE notice_id='";
    private final static String GET_RECEIVED_NOTICE = "SELECT * FROM notice,user_information,house_information" +
    												  " WHERE notice.user_id=user_information.user_id" +
													  " AND notice.house_id=house_information.house_id" +
													  " AND house_information.user_id='";
    private final static String GET_ALL_NOTICE = "SELECT * FROM notice ORDER BY notice_id DESC";
    private final static String DELETE_NOTICE = "DELETE FROM notice WHERE notice_id='";
    
    public static void deleteNotice(int notice_id)
	{
		try
    	{
    		DBManager dbManager = new DBManager();
    		dbManager.executeUpdate(DELETE_NOTICE + notice_id + "'");
    		dbManager.close();
    	}
    	catch (SQLException ex)
    	{
    		Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
    	}
	}
    
    public static ArrayList getAllNotice()
    {
    	ArrayList list = new ArrayList();
    	try
    	{
    		DBManager dbManager = new DBManager();
    		ResultSet result = dbManager.executeQuery(GET_ALL_NOTICE);
    		while(result.next())
    		{
				Notice notice = new Notice();
				notice.setUser_id(result.getInt(USER_ID));
				notice.setNotice_id(result.getInt(NOTICE_ID)); 
				notice.setKnow(result.getString(KNOW)); 
    			notice.setHouse_id(result.getInt(HOUSE_ID));
                list.add(notice);
    		}
    		dbManager.close();
    	}
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return list;
    }
    
    public static ArrayList getReceivedNotice(int user_id)
    {
    	ArrayList list = new ArrayList();
    	try
    	{
    		DBManager dbManager = new DBManager();
    		ResultSet result = dbManager.executeQuery(GET_RECEIVED_NOTICE + user_id + "' ORDER BY notice_id DESC");
    		while(result.next())
    		{
				Notice notice = new Notice();
				notice.setUser_id(result.getInt(USER_ID));
				notice.setNotice_id(result.getInt(NOTICE_ID)); 
				notice.setKnow(result.getString(KNOW)); 
    			notice.setHouse_id(result.getInt(HOUSE_ID));
    			notice.setHouse_title(result.getString(HOUSE_TITLE));
    			notice.setUser_name(result.getString(USER_NAME));
    			notice.setPhone_number(result.getString(PHONE_NUMBER));
                list.add(notice);
    		}
    		dbManager.close();
    	}
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return list;
    }
    
    public static void updateNoticeAsKnown(int notice_id)
    {
    	try
    	{
    		DBManager dbManager = new DBManager();
    		dbManager.executeUpdate(CHANGE_NOTICE_AS_KNOWN + notice_id + "' ");
    		dbManager.close();
    	}
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public static ArrayList getNoticeAndUser(int user_id)
    {
    	ArrayList list = new ArrayList();
    	try
    	{
    		DBManager dbManager = new DBManager();
    		ResultSet result = dbManager.executeQuery(GET_NOTICE_AND_USER_INFORMATION + user_id + "' ORDER BY notice_id DESC");
    		while(result.next())
    		{
				Notice notice = new Notice();
				notice.setUser_id(result.getInt(USER_ID));
				notice.setNotice_id(result.getInt(NOTICE_ID)); 
				notice.setKnow(result.getString(KNOW)); 
    			notice.setHouse_id(result.getInt(HOUSE_ID));
    			notice.setHouse_title(result.getString(HOUSE_TITLE));
    			notice.setUser_name(result.getString(USER_NAME));
    			notice.setPhone_number(result.getString(PHONE_NUMBER));
                list.add(notice);
    		}
    		dbManager.close();
    	}
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return list;
    }
    
    public static ArrayList getUserNotice(int user_id)
    {
    	ArrayList list = new ArrayList();
    	try
    	{
    		DBManager dbManager = new DBManager();
    		ResultSet result = dbManager.executeQuery(GET_USER_NOTICE + user_id + "' ORDER BY notice_id DESC");
    		while(result.next())
    		{
				Notice notice = new Notice();
				notice.setUser_id(result.getInt(USER_ID));
				notice.setNotice_id(result.getInt(NOTICE_ID)); 
				notice.setKnow(result.getString(KNOW)); 
    			notice.setHouse_id(result.getInt(HOUSE_ID));
    			notice.setHouse_title(result.getString(HOUSE_TITLE));
                list.add(notice);
    		}
    		dbManager.close();
    	}
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return list;
    }
    
    public static int checkUserNoticePost(int user_id, int house_id)
    {
    	try
        {
            DBManager dbManager = new DBManager();
            ResultSet result = dbManager.executeQuery(HAS_POSTED_NOTICE_CHECK);
            while(result.next())
            {
            	if(result.getInt(USER_ID) == user_id && result.getInt(HOUSE_ID) == house_id)
            	{
            		return 1;
            	}
            }
            dbManager.close();
        }
        catch (SQLException ex)
        {
            Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }
    
    public static int getNotice_idForNew()
    {
        int maxUid = 0;
        try
        {
            DBManager dbManager = new DBManager();
            ResultSet result = dbManager.executeQuery(LOCK_TABLE+GET_MAX_NOTICE_ID);
            result.next();
            maxUid = result.getInt(1);
            dbManager.close();
        }
        catch (SQLException ex)
        {
            Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return maxUid+1;
    }
    
    public static void insertNewNotice(int user_id, int house_id)
    {
    	int notice_id = getNotice_idForNew();
    	try
    	{
    		DBManager dbManager = new DBManager();
    		dbManager.executeUpdate(INSERT_NOTICE_HEAD +
    				user_id + "','" + house_id + "','" + notice_id + "','" + "未读" + INSERT_NOTICE_FOOT);
    		dbManager.close();
    	}
    	catch (SQLException ex)
    	{
    		Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
    	}
    }
}
